﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using DataAccess;
using System.Collections;
using System.Configuration;
using System.Data.SqlClient;


namespace Business {
    public class TrendReportService {
        public DataTable getMachineMatrixByMachineID(int machine_id) {
            CreateConnection cn = new CreateConnection();
            DataTable dt = new DataTable();
            string query = "select CONVERT(varchar(10), m.date_recorded, 3) AS date,machine_id, matrix_no from MachineMatrix m where (machine_id =" + machine_id + ") ORDER BY date_recorded DESC;";
            dt = cn.CreateDataTable(query);
            return dt;
        }
        /*   public DataTable getMatrixByMachineidAndDateRange(int machine_id, string fromDate, string toDate)
           {
               string query = "select dateRecorded, matrix_no from machinematrix where machine_
           }*/
        public void addNewMatrix(int machine_id, string date, string matrixNo) {
            CreateConnection cn = new CreateConnection();
            string query = "INSERT INTO MachineMatrix(date_recorded, machine_id, matrix_no)VALUES (CONVERT(date, '" + date + "', 103), " + machine_id + ", " + matrixNo + ");";
            cn.RunCommand(query);
        }
        public void updateMatrixInDateperiod(DataTable oldList, DataTable newList, int machine_id) {


            CreateConnection cn = new CreateConnection();
            string queryDelete = "";//DELETE MachineMatrix where component_id=" + component_id + ";";
            string queryInsert = "";
            foreach (DataRow r in oldList.Rows) {
                string olddate = r["x"].ToString();
                // string value = r["y"].ToString();
                queryDelete = queryDelete + "DELETE MachineMatrix where machine_id=" + machine_id + " and convert(varchar(10),date_recorded,3) = '" + olddate + "' ;";

            }
            cn.RunCommand(queryDelete);

            foreach (DataRow nr in newList.Rows) {
                string newdate = nr["x"].ToString();
                string value = nr["y"].ToString();
                string t = newdate.Insert(6, "20");
                queryInsert = queryInsert + "INSERT INTO MachineMatrix(date_recorded, machine_id, matrix_no)VALUES (CONVERT(date, '" + t + "', 103), " + machine_id + ", " + value + ");";

            }
            if (queryInsert != "")
                cn.RunCommand(queryInsert);

        }
        public void deleteMatrix(DataTable List, int machine_id) {


            CreateConnection cn = new CreateConnection();
            string queryDelete = "";//DELETE MachineMatrix where component_id=" + component_id + ";";
            foreach (DataRow r in List.Rows) {
                string olddate = r["x"].ToString();
                // string value = r["y"].ToString();
                queryDelete = queryDelete + "DELETE MachineMatrix where machine_id=" + machine_id + " and convert(varchar(10),date_recorded,3) = '" + olddate + "' ;";

            }
            if (queryDelete != "")
                cn.RunCommand(queryDelete);
        }

        public void updateMatrix(DataTable newList, int machine_id) {


            CreateConnection cn = new CreateConnection();
            string queryUpdate = "";//DELETE MachineMatrix where component_id=" + component_id + ";";
            string queryInsert = "";
            foreach (DataRow nr in newList.Rows) {
                string newdate = nr["x"].ToString();
                string value = nr["y"].ToString();
                string t = newdate.Insert(6, "20");
                queryInsert = queryInsert + "INSERT INTO MachineMatrix(date_recorded, machine_id, matrix_no)VALUES (CONVERT(date, '" + t + "', 103), " + machine_id + ", " + value + ");";
                queryUpdate = queryUpdate + "UPDATE MachineMatrix SET matrix_no =" + value + "  where machine_id=" + machine_id + " and convert(varchar(10),date_recorded,3) = '" + newdate + "' ;";

            }
            if (queryUpdate != "")
                cn.RunCommand(queryUpdate);
            if (queryInsert != "")
                cn.RunCommand(queryInsert);

        }
    }
}
